﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ConnectDB
/// </summary>
public class ConnectDB
{
    public SqlConnection conn;

    public ConnectDB()
    {
        //
        // TODO: Add constructor logic here
        //
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["cinemaconnection.Properties.Settings.cinemaConnectionString"].ConnectionString);
    }

    public Boolean getUserAdmin(String username, String password)
    {
        String user = ConfigurationManager.AppSettings.GetValues("username")[0].ToString();
        String pass = ConfigurationManager.AppSettings.GetValues("password")[0].ToString();
        if (user == username && password == pass)
            return true;
        else return false;
    }

    public DataSet LoadFilmSchedule(DateTime dateFilmShow, int iCinema)
    {
        string strQuery = "Select NameVN, TimeShow1,TimeShow2,TimeShow3,TimeShow4,TimeShow5,TimeShow6,TimeShow7,TimeShow8 from Film where DateShow='" +
                            dateFilmShow.ToShortDateString() + "' and Cinema = " + iCinema.ToString();
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public DataSet LoadFilmEnSchedule(DateTime dateFilmShow, int iCinema)
    {
        string strQuery = "Select NameEN, TimeShow1,TimeShow2,TimeShow3,TimeShow4,TimeShow5,TimeShow6,TimeShow7,TimeShow8 from Film where DateShow='" +
                            dateFilmShow.ToShortDateString() + "' and Cinema = " + iCinema.ToString();
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public DataSet LoadAllFilm()
    {
        string strQuery = "Select NameVN,NameEN,Cinema, TimeShow1,TimeShow2,TimeShow3," +
        "TimeShow4,TimeShow5,TimeShow6,TimeShow7,TimeShow8,FilmId, DateShow from Film order by DateShow, Cinema";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public Boolean DeleteFilm(int iFilmId)
    {
        string strQuery = "delete from Film where FilmId=" + iFilmId.ToString();
        conn.Open();
        SqlCommand cmd = new SqlCommand(strQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public Boolean AddFilm(Film f)
    {
        String sqlQuery = "Insert Into Film(FilmId, NameVN, NameEN, Cinema, Image, DateShow, TimeShow1, TimeShow2," +
            "TimeShow3, TimeShow4,TimeShow5, TimeShow6, TimeShow7, TimeShow8)" +
                           " values (" + f.IFilmID + ",N'" + f.StrFilmNameVn + "',N'" +
                                      f.StrFilmNameEn + "'," + f.IntTheather + ",'" + f.StrImg + "','" +
                                      f.DateFilmShow.ToShortDateString() + "','" +
                                      f.TimeString[0] + "'," +
                                      (f.TimeString[1] != "null" ? "'" + f.TimeString[1] + "'," : "null,") +
                                      (f.TimeString[2] != "null" ? "'" + f.TimeString[2] + "'," : "null,") +
                                      (f.TimeString[3] != "null" ? "'" + f.TimeString[3] + "'," : "null,") +
                                      (f.TimeString[4] != "null" ? "'" + f.TimeString[4] + "'," : "null,") +
                                      (f.TimeString[5] != "null" ? "'" + f.TimeString[5] + "'," : "null,") +
                                      (f.TimeString[6] != "null" ? "'" + f.TimeString[6] + "'," : "null,") +
                                      (f.TimeString[7] != "null" ? "'" + f.TimeString[7] + "'" : "null") + ")";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet MaxFilmID()
    {
        string strQuery = "Select Max(filmID) from film";
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }


    public DataSet LoadFilmById(Int32 id)
    {
        string strQuery = "Select * from Film where FilmID = " + id;
        DataSet ds = new DataSet();

        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

    public Boolean EditFilm(Film f)
    {
        String sqlQuery = "Update Film Set NameVN = N'" + f.StrFilmNameVn + "', NameEN = N'" + f.StrFilmNameEn + "'," +
                                        " TimeShow1 = '" + f.TimeString[0] +
                                        "', TimeShow2 = " + (f.TimeString[1] != "null" ? "'" + f.TimeString[1] + "'," : "null,") +
                                        " TimeShow3 = " + (f.TimeString[2] != "null" ? "'" + f.TimeString[2] + "'," : "null,") +
                                        " TimeShow4 = " + (f.TimeString[3] != "null" ? "'" + f.TimeString[3] + "'," : "null,") +
                                        " TimeShow5 = " + (f.TimeString[4] != "null" ? "'" + f.TimeString[4] + "'," : "null,") +
                                        " TimeShow6 = " + (f.TimeString[5] != "null" ? "'" + f.TimeString[5] + "'," : "null,") +
                                        " TimeShow7 = " + (f.TimeString[6] != "null" ? "'" + f.TimeString[6] + "'," : "null,") +
                                        " TimeShow8 = " + (f.TimeString[7] != "null" ? "'" + f.TimeString[7] + "' " : "null ") +
                                        "where FilmID = " + f.IFilmID;

        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            conn.Close();
            return false;
        }
        conn.Close();
        return true;
    }

    public DataSet LoadPoster(DateTime dtStart, DateTime dtEnd, int iCinema)
    {
        string strQuery = "Select distinct NameVN, NameEN, Cinema, Image from film where Cinema=" + iCinema.ToString() + " and DateShow>='" +
                                    dtStart.ToShortDateString() + "' and DateShow<='" + dtEnd.ToShortDateString() + "'";
        DataSet ds = new DataSet();
        conn.Open();
        SqlDataAdapter adap = new SqlDataAdapter(strQuery, conn);
        adap.Fill(ds);
        conn.Close();

        return ds;
    }

}
